create external table jms_dm.dm_terminal_deliver_punctuality_rate_detail_dt (
   billcode string comment '运单',
   end_agent_code string comment '末端代理区code',
   end_agent_name string comment '末端代理区name',
   end_franchisee_code string comment '末端加盟商code',
   end_franchisee_name string comment '末端加盟商',
   send_next_station_code string comment '下一站code',
   send_next_station_name string comment '下一站',
   deliver_network_code string comment '出仓网点code',
   deliver_network_name string comment '出仓网点',
   end_provider_id string comment '末端省id',
   end_provider_name string comment '末端省',
   end_city_id string comment '末端城市',
   end_city_name string comment '末端城市',
   end_center_code string comment '末中心code',
   end_center_name string comment '末中心',
   end_center_send_time string comment '末端中心发件时间',
   planned_departure_time string comment '规划接件时间',
   planned_arrival_time string comment '规划到件时间',
   new_effective_plan_sign_time string comment '规划派件时间',
   span_days_shift string comment '派件跨天',
   deliver_shift string comment '派件班次',
   end_network_arrival_time string comment ' 末端网点到件时间',
   deliver_time string comment '出仓时间',
   deliver_type int comment '派件类型  1稍后出仓 2到出一体 3出仓 4其它',
   order_source_code string comment '订单来源code',
   order_source_name string comment ' 订单来源',
   deliver_township_id string comment '出仓乡镇id',
   deliver_township_name string comment '出仓乡镇',
   is_common int comment '是否共配',
   new_effective_plan_deliver_date string comment '规划派件时间',
   date_time string comment '发件日期'
   ,end_network_code string comment '末端到件网点code'  --
   ,end_network_name string comment '末端到件网点'  --
   ,final_deliver_network_code string comment '最终派件网点code' --
   ,final_deliver_network_name string comment '最终派件网点'
   ,is_deliver_punctuality int comment '是否派件准点'
   ,delay_minutes decimal(16,2) comment '延误分钟'
   ,plan_warehouse_end_time string comment '规划派仓派件结束时间'
   ,is_township int comment '是否乡镇件'
   ,special_extra_time decimal(16,2) comment '乡镇加时'--
) COMMENT '末端派件及时率明细'
PARTITIONED BY (dt STRING COMMENT '分区日期')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_terminal_deliver_punctuality_rate_detail_dt'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);

alter table jms_dm.dm_terminal_deliver_punctuality_rate_detail_dt add columns (
        final_sign_user_code string comment '派件员code'
       ,final_sign_user_name string comment '派件员'
    )cascade ;